import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author zsy
 * @Description:
 * @create 2018-01-04 11:04
 **/
public class ExcelUtils {

    private static List<String> getHeadName(XSSFSheet sheet) {
        List<String> list = new ArrayList<>();
        for (Cell cell : sheet.getRow(sheet.getFirstRowNum())) {
            list.add(cell.getStringCellValue());
        }
        return list;
    }

    public static List<Map<String, Object>> readExcel2Map(String fileName, String sheetName)
            throws Exception {
        File file = new File(fileName);
        if (!file.exists()) {
            throw new Exception("找不到文件" + fileName);
        }
        List<Map<String, Object>> list = new LinkedList<>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));

        XSSFSheet sheet = xwb.getSheet(sheetName);
        //列名
        List<String> headName = getHeadName(sheet);

        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = (sheet.getFirstRowNum() + 1); i <= (sheet.getPhysicalNumberOfRows() - 1);
                i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            Map<String, Object> map = new HashMap<>();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                Object value = null;
                cell = row.getCell(j);
                if (cell == null) {
                    String errorMessage =
                            fileName + "文件的" + sheetName + "表的" + (i + 1) + "行" + (j + 1) + "列有错误";
                    throw new Exception(errorMessage);
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        //String类型返回String数据
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        //数值类型返回double类型的数字
                        value = cell.getNumericCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        //布尔类型
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                    case XSSFCell.CELL_TYPE_BLANK:
                        //空单元格,错误单元格
                        break;
                    default:
                        value = cell.toString();
                }
                if (value != null && !value.equals("")) {
                    //单元格正确，则加入列表
                    map.put(headName.get(j), value);
                } else {
                    String errorMessage =
                            fileName + "文件的" + sheetName + "表的" + (i + 1) + "行" + (j + 1) + "列有错误";
                    throw new Exception(errorMessage);
                }
            }
            list.add(map);
        }
        return list;
    }


}
